<?php
/**
 * Method naming convention:
 * get...() returns one record with all its fields and no associations fetched. The record can be passed to update() method.
 * fetch...() returns one record with meaningful associations fetched or with fields from associated tables
 * find...() returns an array of records with no restrictions
 * count...() returns a number
 * query...() accepts an SQL query and its parameters
 * ordered...() returns an array of records, accepts $tableUuid used to determine right order of the query
 * options...() returns an array of key+value pairs
 */
class YoseminDao {

    protected $db;
    private static $instance;

    private function __construct($db) {
    	$this->db = $db;
    }
    
    public static function instance() {
    	if (self::$instance == null) {
	    	$dbUser = Kohana::config('yosemin.db_user');
	    	$dbPassword = Kohana::config('yosemin.db_password');
	    	$pdoConnectionString = Kohana::config('yosemin.pdo_connection_string');
	    	$db = new PDO($pdoConnectionString, $dbUser, $dbPassword);
	    	
	    	$initQueries = Kohana::config('yosemin.db_init_queries');
	    	if ($initQueries) {
	    		foreach ($initQueries as $query) {
	    			$statement = $db->prepare($query);
	    			$statement->execute();
	    			$statement->closeCursor();
	    		}
	    	}
	    	
	    	// PDO::ERRMODE_SILENT: Just set error codes.
	    	// PDO::ERRMODE_WARNING: Raise E_WARNING.
	    	// PDO::ERRMODE_EXCEPTION: Throw exceptions.
	    	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	    	
	    	self::$instance = new YoseminDao($db);
    	}
    	return self::$instance;
    }

    public function getDb() {
        return $this->db;
    }

    /**
     * @param Entity_Model $entity
     */
    public function insertEntity($entity) {
        return $this->insert($entity->getTableName(), $entity->getMap(), array(), $entity->hasEntityVersion(), $entity->hasCreationFields(), $entity->hasModificationFields());
    }
    
    public function insert($table, $namedValues, $excludeKeys=array(), $setEntityVersion=true, $setCreationFields=true, $setModificationFields=true) {
    	if (! $excludeKeys) {
    		$excludeKeys = array();
    	}
        else if (! is_array($excludeKeys)) {
            $excludeKeys = array($excludeKeys);
        }
        $excludeKeys[] = 'submit';
        $excludeKeys[] = 'id';
        $excludeKeys[] = auth::$FORM_ID;
        
        $sessionData = SessionData::instance();
        $user = $sessionData->getAuthUser();
        if ($user) {
        	if ($setCreationFields) {
            	$namedValues['created_by_id'] = $user['id'];
        	}
        	if ($setModificationFields) {
            	$namedValues['modified_by_id'] = $user['id'];
        	}
        }
        $now = time();
        if ($setCreationFields) {
        	$namedValues['created_at'] = $now;
        }
        if ($setModificationFields) {
        	$namedValues['modified_at'] = $now;
        }

        if ($setEntityVersion) {
        	$namedValues[sql::$ENTITY_VERSION_KEY] = 1;
        }

        $values = array();
        $query = 'INSERT INTO ' . $table . ' (';
        foreach ($namedValues as $name => $value) {
            if (array_search($name, $excludeKeys) === FALSE && ! is_array($value) && substr($name, -4, 4) != '_ids') {
                $query .= $name . ', ';
                // Security (Cross-site scripting) - remove HTML tags - we don't need them anywhere, so do not allow users to use them.
                if ($value) {
                	$values[$name] = strip_tags($value);
                }
                else {
                	$values[$name] = $value;
                }
            }
        }
        $query = substr($query, 0, -2);
        $query .= ') VALUES (';
        foreach ($values as $name => $value) {
            $query .= ':' . $name . ', ';
        }
        $query = substr($query, 0, -2);
        $query .= ')';

        $statement = $this->db->prepare($query);
        $statement->execute($values);
        $statement->closeCursor();
        $id = $this->db->lastInsertId();
        return $id;
    }
    
    /**
     * @param Entity_Model $entity
     */
    public function updateEntity($entity) {
        return $this->update($entity->getTableName(), $entity->getMap(), $entity->getPrimaryKeyList(), array(), $entity->hasEntityVersion(), $entity->hasModificationFields());
    }

    public function update($table, $namedValues, $pkFields=array('id'), $excludeKeys=array(), $setEntityVersion=true, $setModificationFields=true) {
        if (! is_array($pkFields)) {
            $pkFields = array($pkFields);
        }
        if (! is_array($excludeKeys)) {
            $excludeKeys = array($excludeKeys);
        }

        $excludeKeys[] = 'submit';
        $excludeKeys[] = auth::$FORM_ID;
        foreach ($pkFields as $pk) {
		    $excludeKeys[] = $pk;
		}

		if ($setModificationFields) {
	        $sessionData = SessionData::instance();
	        $user = $sessionData->getAuthUser();
	        if ($user) {
	            $namedValues['modified_by_id'] = $user['id'];
	        }
	        $now = time();
	        $namedValues['modified_at'] = $now;
		}

        $entityVersion = intVal($namedValues[sql::$ENTITY_VERSION_KEY]);
        if ($entityVersion) {
            $namedValues[sql::$ENTITY_VERSION_KEY] = $entityVersion + 1;
        }

        $values = array();
        $query = 'UPDATE ' . $table . ' SET ';
        foreach ($namedValues as $name => $value) {
            // Skip all numeric keys, they are just duplicates from a select query.
            if (is_numeric($name)) {
                continue;
            }
            // Update only non-primary key columns
            if (array_search($name, $excludeKeys) === FALSE && ! array_search($name, $pkFields) && substr($name, -4, 4) != '_ids') {
                $query .= $name . ' = :' . $name . ', ';
                // Security (Cross-site scripting) - remove HTML tags - we don't need them anywhere, so do not allow users to use them.
                if ($value) {
                	$values[$name] = strip_tags($value);
                }
                else {
                	$values[$name] = $value;
                }
            }
        }
        $query = substr($query, 0, -2);

        $query .= ' WHERE ';
        foreach ($pkFields as $name) {
            $query .= $name . ' = :' . $name . ' AND ';
            $value = $namedValues[$name];
            if ($value) {
            	$values[$name] = strip_tags($value);
            }
            else {
            	$values[$name] = $value;
            }
        }
        if ($entityVersion) {
            $query .= sql::$ENTITY_VERSION_KEY . ' = ' . $entityVersion;
        }
        else {
            $query = substr($query, 0, -4);
        }

        $statement = $this->db->prepare($query);
        $statement->execute($values);
        $statement->closeCursor();
    }
    
    /**
     * @param Entity_Model $entity
     */
    public function deleteEntity($entity) {
        return $this->delete($entity->getTableName(), $entity->getMap(), $entity->getPkValues());
    }

    public function delete($table, $pkNamedValues) {
        $query = 'DELETE FROM ' . $table . ' WHERE ';
        if (! is_array($pkNamedValues)) {
            $pkNamedValues = array('id' => $pkNamedValues);
        }
        foreach ($pkNamedValues as $name => $value) {
            $query .= $name . ' = :' . $name . ' AND ';
        }
        $query = substr($query, 0, -4);
        $statement = $this->db->prepare($query);
        $statement->execute($pkNamedValues);
        $statement->closeCursor();
    }

    public function deleteByCode($table, $code) {
        return $this->delete($table, array('code' => $code));
    }
    
    public function deleteById($table, $id) {
        return $this->delete($table, array('id' => $id));
    }

    public function getById($table, $id) {
        return $this->getUnique($table, array('id' => $id));
    }

    public function getByCode($table, $code) {
        return $this->getUnique($table, array('code' => $code));
    }

    public function findByValues($table, $field, $valueList, $order=NULL) {
        $query = 'SELECT * FROM ' . $table . ' WHERE ' . $field . ' IN (' . sql::inClauseValues($valueList) . ')';
        if ($order) {
            $query .= ' ORDER BY ' . $order;
        }
        $statement = $this->db->prepare($query);
        $statement->execute();
        $result = array();
        while ($row = $statement->fetch()) {
            $result[$row[$field]] = $row;
        }
        $statement->closeCursor();
        return $result;
    }

    public function findByIds($table, $idList) {
        return $this->findByValues($table, 'id', $idList);
    }

    public function findByCodes($table, $codeList, $order=NULL) {
        return $this->findByValues($table, 'code', $codeList, $order);
    }

    public function alreadyExists($table, $columns, $entity, $pkField='id') {
        if (! is_array($columns)) {
            $columns = array($columns);
        }
        $params = array();

        $pk = $entity[$pkField];
        
        $queryString = 'SELECT count(*) FROM ' . $table . ' WHERE ';
        foreach ($columns as $columnName) {
            $params[$columnName] = $entity[$columnName];
            $queryString .= $columnName . ' = :' . $columnName . ' AND ';
        }
        $queryString = substr($queryString, 0, -4);
        if ($pk) {
            $params[$pkField] = $pk;
            $queryString .= ' AND ' . $pkField . ' != :' . $pkField;
        }
        $statement = $this->db->prepare($queryString);
        $statement->execute($params);
        $count = $statement->fetchColumn();
        $statement->closeCursor();
        return $count > 0;
    }

    public function findLocalized($table, $languageCode, $pkField='id', $pk = NULL) {
        $params = array('language_code' => $languageCode);
        $query = 'SELECT obj.*, l10n.* ' .
            'FROM ' . $table . ' obj ' .
            'LEFT OUTER JOIN ' . $table . '_l10n l10n ON obj.' . $pkField . ' = l10n.' . $table . '_' . $pkField . ' AND l10n.language_code = :language_code ';
        if ($pk !== NULL) {
            $query .= 'WHERE obj.' . $pkField . ' = :pk';
            $params['pk'] = $pk;
            return $this->queryOne($query, $params);
        }
        return $this->queryAll($query, $params);
    }

    public function getLocalizedEntityByCode($tableName, $code, $languageCode) {
        return $this->findLocalized($tableName, $languageCode, 'code', $code);
    }

    public function getLocalizedEntityById($tableName, $id, $languageCode) {
        return $this->findLocalized($tableName, $languageCode, 'id', $id);
    }
    
    public function getLocalizedBatch($seminarId, $batchCode, $languageCode) {
        return $this->queryOne(
        		'SELECT b.*, l10n.name, l10n.description ' .
				'FROM batch b ' .
				'LEFT OUTER JOIN batch_l10n l10n ON b.batch_code = l10n.batch_code AND b.seminar_id = l10n.seminar_id AND l10n.language_code = :language_code ' .
				'WHERE b.seminar_id = :seminarId ' .
				'AND b.batch_code = :batchCode',
        		array('seminarId' => $seminarId, 'batchCode' => $batchCode, 'language_code' => $languageCode));
    }

    /**
     * @param Entity_Model $entity
     */
    public function getByPrimaryKey($entity) {
        return $this->getUnique($entity->getTableName(), $entity->getPkValues());
    }
    
    public function getUnique($table, $columns) {
        $queryString = 'SELECT * FROM ' . $table . ' WHERE ';
        foreach ($columns as $columnName => $value) {
            $queryString .= $columnName . ' = :' . $columnName . ' AND ';
        }
        $queryString = substr($queryString, 0, -4);
        $statement = $this->db->prepare($queryString);
        $statement->setFetchMode(PDO::FETCH_ASSOC);
        $statement->execute($columns);
        $object = $statement->fetch();
        $statement->closeCursor();
        return $object;
    }

    public function getUniqueByFieldsOfEntity($table, $entity, $fields) {
        $columnValueArray = array();
        if (! is_array($fields)) {
            $fields = array($fields);
        }
        foreach ($fields as $column) {
            $columnValueArray[$column] = $entity[$column];
        }
        return $this->getUnique($table, $columnValueArray);
    }

    public function getByFields($table, $columnValueArray, $orderBy=NULL, $indexByField=NULL) {
        $queryString = 'SELECT * FROM ' . $table . ' WHERE ';
        foreach ($columnValueArray as $columnName => $value) {
            $queryString .= $columnName . ' = :' . $columnName . ' AND ';
        }
        $queryString = substr($queryString, 0, -4);
        if ($orderBy) {
            $queryString .= ' ORDER BY ' . $orderBy;
        }
        $statement = $this->db->prepare($queryString);
        $statement->execute($columnValueArray);
        if ($indexByField) {
            $result = array();
            while ($row = $statement->fetch()) {
                $result[$row[$indexByField]] = $row;
            }
        }
        else {
            $result = $statement->fetchAll();
        }
        $statement->closeCursor();
        return $result;
    }

    public function getByField($table, $column, $value, $orderBy=NULL) {
        return $this->getByFields($table, array($column => $value), $orderBy);
    }

    /**
     * Loads the weekend accomodation type with its prices for this seminar.
     */
    public function fetchViAccomodationTypeForSeminar($viSeminarId, $atCode) {
        $accomodationType = $this->queryOne(
            'SELECT acct.* ' .
            'FROM vi_seminar_accomodation_type sat ' .
            'JOIN vi_accomodation_type acct ON acct.code = sat.vi_accomodation_type_code ' .
            'WHERE sat.vi_seminar_id = :vi_seminar_id ' .
            'AND acct.code = :vi_accomodation_type_code',
            array(
                'vi_seminar_id' => $viSeminarId,
                'vi_accomodation_type_code' => $atCode
            ));
        if ($accomodationType) {
            $priceList = $this->getByFields('vi_seminar_accomodation_type_price',
                    array(
                        'vi_seminar_id' => $viSeminarId,
                        'vi_accomodation_type_code' => $atCode
                    ));
            foreach ($priceList as $price) {
                $accomodationType['price_' . $price['currency_code']] = $price['price'];
            }
        }

        return $accomodationType;
    }

    public function fetchViAccomodationTypeListForSeminar($viSeminarId) {
        $viAccomodationTypeList = $this->queryAll(
                'SELECT acct.* ' .
                'FROM vi_seminar_accomodation_type sat ' .
                'JOIN vi_accomodation_type acct ON acct.code = sat.vi_accomodation_type_code ' .
                'WHERE sat.vi_seminar_id = :vi_seminar_id',
                array('vi_seminar_id' => $viSeminarId));
        for ($i = 0; $i < count($viAccomodationTypeList); $i++) {
            $atPriceList = $this->getByFields('vi_seminar_accomodation_type_price',
                    array(
                        'vi_seminar_id' => $viSeminarId,
                        'vi_accomodation_type_code' => $viAccomodationTypeList[$i]['code']
                    ));
            foreach ($atPriceList as $price) {
                $viAccomodationTypeList[$i]['price_' . $price['currency_code']] = $price['price'];
            }
        }
        return $viAccomodationTypeList;
    }

    public function queryAll($sqlQuery, $params=NULL) {
    	if ($sqlQuery instanceof QueryAndParams) {
    		$sqlQuery = $sqlQuery->query;
    		$params = $sqlQuery->params;
    	}
        $statement = $this->db->prepare($sqlQuery);
        if ($params) {
            $statement->execute($params);
        }
        else {
            $statement->execute();
        }
        $result = $statement->fetchAll();
        $statement->closeCursor();
        return $result;
    }

    public function queryOne($sqlQuery, $params=NULL) {
        $result = $this->queryAll($sqlQuery, $params);
        if (count($result) > 1) {
            throw new Exception('More than one result returned.');
        }
        else if (count($result) == 1) {
            return $result[0];
        }
        return NULL;
    }

    public function queryOneValue($sqlQuery, $params=NULL) {
        $result = $this->queryAll($sqlQuery, $params);
        if (count($result) > 1) {
            throw new Exception('More than one result returned.');
        }
        else if (count($result) == 1) {
            return $result[0][0];
        }
        return NULL;
    }

    /**
     * Returns an array of first values of the query. So that the result is not array of arrays as is the result from queryAll().
     * The method is useful for querying values for selectMany components.
     */
    public function queryFirstValues($sqlQuery, $params=NULL) {
        $result = $this->queryAll($sqlQuery, $params);
        $valueList = array();
        foreach ($result as $row) {
            $valueList[] = $row[0];
        }
        return $valueList;
    }

    public function queryCount($queryString, $params = NULL) {
        if (! $params) {
            $params = array();
        }
        $statement = $this->db->prepare($queryString);
        $statement->execute($params);
        $count = $statement->fetchColumn();
        $statement->closeCursor();
        return $count;
    }

    public function queryOptions($queryString, $params=NULL, $delimiter=NULL) {
        $statement = $this->db->prepare($queryString);
        if ($params) {
            $statement->execute($params);
        }
        else {
            $statement->execute();
        }
        $options = array();
        while ($row = $statement->fetch()) {
            $label = $row[1];
            if ($delimiter) {
                if (! is_array($delimiter)) {
                    $delimiter = array($delimiter);
                }
                for ($i = 0; $i < count($delimiter); $i++) {
                    $value = $row[$i + 2];
                    if (valid::isIsoDate($value)) {
                        $value = format::date($value);
                    }
                    if ($delimiter[$i] == '()') {
                        $label .= ' (' . $value . ')';
                    }
                    else {
                        $label .= $delimiter[$i] . $value;
                    }
                }
            }
            $options[$row[0]] = $label;
        }
        $statement->closeCursor();
        return $options;
    }

    public function executeQuery($query, $params) {
        $statement = $this->db->prepare($query);
        $statement->execute($params);
        $statement->closeCursor();
    }

    public function fetchSeminar($seminarId, $languageCode) {
        $query = 'SELECT sem.*, l10n.name, l10n.description ' .
                 'FROM seminar sem ' .
                 'LEFT OUTER JOIN seminar_l10n l10n ON l10n.seminar_id = sem.id AND l10n.language_code = :language_code ' .
                 'WHERE sem.id = :id';
        return $this->queryOne($query, array('id' => $seminarId, 'language_code' => $languageCode));
    }

    public function fetchViSeminarWithPrices($viSeminarId) {
        $viSeminar = $this->getById('vi_seminar', $viSeminarId);
        if ($viSeminar) {
            $priceList = $this->getByField('vi_seminar_price', 'vi_seminar_id', $viSeminarId);
            foreach ($priceList as $price) {
                $viSeminar['prices-' . $price['currency_code']] = $price;
            }
        }
        return $viSeminar;
    }

    public function getViSeminarPricesByCurrency($viSeminarId, $currencyCode) {
        $prices = $this->queryOne('SELECT * FROM vi_seminar_price ' .
                'WHERE vi_seminar_id = :vi_seminar_id AND currency_code = :currency_code',
                array('vi_seminar_id' => $viSeminarId, 'currency_code' => $currencyCode));
        return $prices;
    }

    public function getViSeminarAccomodationPrice($viSeminarId, $viAccomodationTypeCode, $currencyCode) {
        $price = $this->queryOne('SELECT * FROM vi_seminar_accomodation_type_price ' .
                'WHERE vi_seminar_id = :vi_seminar_id AND vi_accomodation_type_code = :vi_accomodation_type_code AND currency_code = :currency_code',
                array(
                    'vi_seminar_id' => $viSeminarId,
                    'currency_code' => $currencyCode,
                    'vi_accomodation_type_code' => $viAccomodationTypeCode
                ));
        return $price;
    }

    public function selectViSeminarCurrecyCodeList($viSeminarId) {
        $priceList = $this->getByField('vi_seminar_price', 'vi_seminar_id', $viSeminarId);
        $currencyList = array();
        foreach ($priceList as $price) {
            $currencyList[] = $price['currency_code'];
        }
        return $currencyList;
    }

    public function findViAccomodationTypesWithPrices($viSeminarId) {
        $viAccomodationTypeList = $this->queryAll(
                'SELECT acctype.* ' .
                'FROM vi_seminar_accomodation_type AS semacc ' .
                'LEFT OUTER JOIN vi_accomodation_type AS acctype ON acctype.code = semacc.vi_accomodation_type_code ' .
                'WHERE semacc.vi_seminar_id = :vi_seminar_id',
                array('vi_seminar_id' => $viSeminarId));
        foreach ($viAccomodationTypeList as &$accType) {
            $priceList = $this->getByFields('vi_seminar_accomodation_type_price',
                    array(
                        'vi_seminar_id' => $viSeminarId,
                        'vi_accomodation_type_code' => $accType['code']
                    ));
            foreach ($priceList as $price) {
                $accType['price-' . $price['currency_code']] = $price['price'];
            }
        }
        return $viAccomodationTypeList;
    }

    public function saveMealSelection($mealSelection, $viRegistrationId) {
        $this->executeQuery('DELETE FROM vi_registration_meal WHERE vi_registration_id = :id',
                array('id' => $viRegistrationId));
        foreach ($mealSelection as $mealCode) {
            $meal = substr($mealCode, 11);
            $day = substr($mealCode, 0, 10);
            $mealRecord = array(
                'vi_registration_id' => $viRegistrationId,
                'meal' => $meal,
                'day' => $day
            );
            $this->insert('vi_registration_meal', $mealRecord);
        }
    }

    public function getViSeminarByRegistrationId($viRegistrationId) {
        return $this->queryOne(
                'SELECT sem.* FROM vi_registration AS reg ' .
                'JOIN vi_seminar AS sem ON reg.vi_seminar_id = sem.id ' .
                'WHERE reg.id = :id',
                array('id' => $viRegistrationId));
    }

    public function registrationCountOfViSeminar($viSeminarId) {
        return $this->queryCount(
                'SELECT count(*) FROM vi_registration ' .
                'WHERE vi_seminar_id = :vi_seminar_id ' .
                'AND cancelled_at IS NULL',
                array('vi_seminar_id' => $viSeminarId));
    }

    public function findViRegistrationsByPerson($personId) {
        return $this->queryAll('SELECT reg.*, vat.name AS accomodation_type_name, ' .
                'sem.name AS seminar_name, sem.description AS seminar_desc, ' .
                'sem.begin_date AS seminar_begin_date, sem.end_date AS seminar_end_date ' .
                'FROM vi_registration reg ' .
                'LEFT OUTER JOIN vi_seminar sem ON reg.vi_seminar_id = sem.id ' .
                'LEFT OUTER JOIN vi_accomodation_type vat ON vat.code = reg.vi_accomodation_type_code ' .
                'WHERE person_id = :person_id ' .
                'ORDER BY sem.begin_date DESC, reg.id DESC',
                array('person_id' => $personId));
    }

    public function findRegistrationsByPerson($personId, $languageCode) {
        return $this->queryAll(
                'SELECT reg.*, l10n.name AS seminar_name, l10n.description AS seminar_desc, ' .
                'sem.begin_date AS seminar_begin_date, sem.end_date AS seminar_end_date ' .
                'FROM registration reg ' .
                'LEFT OUTER JOIN seminar sem ON reg.seminar_id = sem.id ' .
                'LEFT OUTER JOIN seminar_l10n l10n ON l10n.seminar_id = sem.id AND l10n.language_code = :language_code ' .
                'WHERE person_id = :person_id ' .
                'ORDER BY sem.begin_date DESC, reg.id DESC',
                array(
                    'language_code' => $languageCode,
                    'person_id' => $personId
                ));
    }
    
    public function fetchPersonById($personId) {
        return $this->queryOne('SELECT p.*, acc.login, acc.password, acc.is_admin, acc.is_blocked, c.name AS country_name, cr.name AS country_of_residence_name, org.name as org_name ' .
                'FROM person p ' .
                'LEFT OUTER JOIN account acc ON acc.person_id = p.id ' .
                'LEFT OUTER JOIN country c ON p.country_code = c.code ' .
                'LEFT OUTER JOIN country cr ON p.country_of_residence_code = cr.code ' .
        		'LEFT OUTER JOIN organization org ON org.id = p.organization_id ' .
                'WHERE p.id = :person_id',
                array('person_id' => $personId));
    }

    public function fetchPersonByLogin($login) {
        return $this->queryOne('SELECT p.*, acc.login, acc.password, acc.is_admin, acc.is_blocked ' .
                'FROM person p ' .
                'JOIN account acc ON acc.person_id = p.id ' .
                'WHERE acc.login = :login',
                array('login' => $login));
    }

    public function existsAccountWithLogin($login) {
        return $this->queryCount('SELECT count(*) FROM account WHERE login = :login', array('login' => $login));
    }

    public function fetchCreatedAndModified($entity) {
        if ($entity['created_by_id']) {
            $entity['created_by'] = $this->fetchPersonById($entity['created_by_id']);
        }
        if ($entity['modified_by_id']) {
            $entity['modified_by'] = $this->fetchPersonById($entity['modified_by_id']);
        }
        return $entity;
    }

    public function findPersonDuplicates() {
        return $this->queryAll('SELECT p.*, acc.login, acc.password, acc.is_admin, acc.is_blocked, count(*) as count ' .
                'FROM person p ' .
                'JOIN account acc ON acc.person_id = p.id ' .
                'GROUP BY p.first_name, p.last_name HAVING count(*) > 1 ' .
                'ORDER BY p.last_name, p.first_name');
    }

    public function findPersonsByTags($tagIds) {
        if (! is_array($tagIds)) {
            $tagIds = array($tagIds);
        }
        return $this->queryAll('SELECT DISTINCT p.* FROM person p ' .
                'JOIN person_tag_asoc tag ON p.id = tag.person_id ' .
                'WHERE tag.person_tag_id IN (' . sql::inClauseValues($tagIds) . ') ' .
                'ORDER BY p.last_name ASC, p.first_name ASC');
    }

    public function findPersonsWithoutTags($tagIds) {
        if (! is_array($tagIds)) {
            $tags = array($tagIds);
        }
        return $this->queryAll('SELECT p.* FROM person p ' .
                'WHERE p.id NOT IN (' .
                'SELECT tag.person_id ' .
                'FROM person_tag_asoc tag ' .
                'WHERE tag.person_tag_code IN (' . sql::inClauseValues($tagIds) . '))');
    }
    
    public function getOrganization($id) {
        $organization = $this->getById('organization', $id);
        if ($organization['organizer_id']) {
            $organization['organizer'] = $this->getById('person', $organization['organizer_id']);
        }
        $organization['country'] = $this->getByCode('country', $organization['country_code']);
        return $organization;
    }
    
    public function countValidRegistrationsBySeminarAndOrg($seminarId, $organizationId) {
        return $this->queryCount(
        		'SELECT count(*) FROM registration ' .
                'WHERE seminar_id = :seminar_id ' .
        		'AND payment_organization_id = :organization_id ' .
        		'AND cancelled_at IS NULL',
                array('seminar_id' => $seminarId, 'organization_id' => $organizationId));
    }
    
    public function countUnconfirmedRegistrationsBySeminarAndOrg($seminarId, $organizationId) {
    	return $this->queryCount(
    			'SELECT count(*) FROM registration ' .
    			'WHERE seminar_id = :seminar_id ' .
    			'AND payment_organization_id = :organization_id ' .
    			'AND cancelled_at IS NULL ' .
    			'AND confirmed_at IS NULL ',
    			array('seminar_id' => $seminarId, 'organization_id' => $organizationId));
    }
    
    public function optionsOrganizationRepCandidate($organizationId) {
        return $this->queryOptions('SELECT p.id, p.first_name, p.last_name, YEAR(p.birth_date) FROM person p ' .
                'WHERE p.id NOT IN (SELECT person_id FROM organization_rep WHERE organization_id = :organization_id) ' .
                'AND p.id NOT IN (SELECT organizer_id FROM organization WHERE id = :organization_id)',
                array('organization_id' => $organizationId),
                array(' ', '()'));
    }
    
    public function optionsOrganizations() {
        return $this->queryOptions('SELECT o.id, o.name, c.name AS country_name ' .
                'FROM organization o ' .
                'JOIN country c ON c.code = o.country_code ',
                NULL, array('()'));
    }
    
    public function optionsOrganizationsByCountry() {
    	$countryList = $this->queryAll(
    			'SELECT * FROM country c ' .
    			'WHERE c.code IN (SELECT country_code FROM organization) ' .
    			'ORDER BY name');
    	$options = array();
    	foreach ($countryList as $country) {
    		$options[$country['name']] = $this->queryOptions(
    				'SELECT o.id, o.name ' .
	    			'FROM organization o ' .
    				'WHERE o.country_code = :country_code ' .
    				'ORDER BY o.name',
	    			array('country_code' => $country['code']));
    	}
    	return $options;
    }
    
    public function orderedOrgRepresentatives($organizationId, $tableUuid) {
        return $this->queryAll(sql::orderBy($tableUuid,
                'SELECT p.*, c.name AS country_name FROM person p ' .
                'JOIN country c ON c.code = p.country_code ' .
                'JOIN organization_rep rep ON rep.person_id = p.id ' .
                'WHERE rep.organization_id = :organization_id', 'last_name'),
                array('organization_id' => $organizationId));
    }
    
    public function orderedOrgMembers($organizationId, $tableUuid) {
        return $this->queryAll(sql::orderBy($tableUuid,
                'SELECT * FROM person WHERE organization_id = :organization_id', 'last_name'),
                array('organization_id' => $organizationId));
    }
    
    public function getSeminarCancellationCharge($seminarId) {
    	return $this->getUnique('seminar_cancellation_charge', array('seminar_id' => $seminarId));
    }
    
    public function optionsCountry($useNativeName=false) {
    	if ($useNativeName) {
    		return $this->queryOptions('SELECT code, name_native, code FROM country ORDER BY name_native ASC', null, '()');
    	}
	    return $this->queryOptions('SELECT code, name, code FROM country ORDER BY name ASC', NULL, '()');
    }
    
    public function getCategory($seminarId, $categoryCode) {
    	return $this->getUnique('category', array('seminar_id' => $seminarId, 'category_code' => $categoryCode));
    }
    
    public function getCategoryPrice($categoryCode, $priceGroupId) {
    	return $this->queryOneValue(
    			'SELECT price FROM category_price ' .
    			'WHERE category_code = :category_code ' .
    			'AND price_group_id = :price_group_id',
    			array (
    					'category_code' => $categoryCode,
    					'price_group_id' => $priceGroupId
    			));
    }
    
    public function getBatch($seminarId, $batchCode) {
    	return $this->getUnique('batch', array('seminar_id' => $seminarId, 'batch_code' => $batchCode));
    }
    
    public function getSeminarSocialDiscount($seminarId) {
    	return $this->getUnique('seminar_social_discount', array('seminar_id' => $seminarId));
    }

    public function getSeminarEarlyDiscount($seminarId) {
    	return $this->getUnique('seminar_early_discount', array('seminar_id' => $seminarId));
    }
    
    public function getSeminarLateCharge($seminarId) {
    	return $this->getUnique('seminar_late_charge', array('seminar_id' => $seminarId));
    }
    
    public function getPersonByPasswordToken($token) {
    	return $this->queryOne(
    			'SELECT p.*, a.* ' .
    			'FROM person p ' .
    			'JOIN account a ON p.id = a.person_id ' .
    			'WHERE change_password_token = :token',
    			array('token' => $token));
    }

    public function getAccount($personId) {
    	return $this->getUnique('account', array('person_id' => $personId));
    }
    
    public function getPerson($personId) {
    	return $this->getUnique('person', array('id' => $personId));
    }
    
    public function nextOrderNumber($tableName) {
    	$entity = $this->queryOne('SELECT order_number FROM ' . $tableName . ' ORDER BY order_number DESC LIMIT 1');
    	if ($entity && $entity['order_number']) {
    		return $entity['order_number'] + 1;
    	}
    	return 1;
    }
    
    public function getPaymentAcceptance($seminarId, $organizationId) {
    	return $this->getUnique('payment_acceptance', array('organization_id' => $organizationId, 'seminar_id' => $seminarId));
    }
}
